package com.yenlien.traceyun.business.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.business.entity.Subpackage;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface SubpackageDao extends BaseJpaDao<Subpackage> {

    @Query(value = "SELECT COUNT(total_number) FROM t_subpackage WHERE company_id = ?1", nativeQuery = true)
    String codeNumCount(String companyId);


    @Query(value = "SELECT IFNULL(SUM(total_number), 0) totalNum FROM t_subpackage WHERE DATE_FORMAT(`createtime`, '%Y-%m') = ?1 AND company_id = ?2", nativeQuery = true)
    Float getTotalNum(String time, String companyId);

    @Query(value = "SELECT IFNULL(SUM(total_number), 0) totalNum FROM t_subpackage WHERE DATE_FORMAT(`createtime`, '%Y-%m') = ?1 AND company_id = ?2 AND product_name = ?3", nativeQuery = true)
    Float getTotalNum(String time, String companyId, String productName);

    @Query(value = "SELECT product_name jyName, total, num, CONCAT( round( num / total * 100.00, 3), '%') percent FROM " +
            "( SELECT * FROM ( SELECT product_name, SUM(total_number) num FROM t_subpackage WHERE company_id = ?1 AND createtime BETWEEN ?2 AND ?3 GROUP BY product_name) t1 " +
            " JOIN (SELECT SUM(total_number) total FROM t_subpackage WHERE company_id = ?1 AND createtime BETWEEN ?2 AND ?3) t2) t", nativeQuery = true)
    List<JSONObject> getPercent(String companyId, String startTime, String endTime);


    Subpackage findById(String id);

    List<Subpackage> findByRelevanceId(String relevanceId);

    @Query("select s from Subpackage s where s.id=(select b.subpackageId from Box b where b.sourceCode=?1)")
    public Subpackage findByCode(String code);

    @Query(value = "select distinct product_name from t_subpackage", nativeQuery = true)
    List<String> findByproductName();

    List<Subpackage> findByProductNameAndCompanyIdIn(String productName, List<String> companyIdIn);

    @Query(value = "select distinct id,branch_package_num,company_id from t_subpackage where batch=?1", nativeQuery = true)
    Map<String, Object> findByBatch(String batch);

    @Query(value = "select sum(s.branchPackageNum) from Subpackage s where s.companyId=?1")
    Object sumBranchPackage(String companyId);


    @Query(value = "SELECT tc.credit_code, tb.source_code `code`, ts.product_name `name`, spec specification, ts.branch_package_num package_specification, ts.spec_units main_unit  " +
            "FROM t_subpackage ts LEFT JOIN t_company tc ON ts.company_id = tc.id LEFT JOIN t_plant_box tb ON ts.id = tb.subpackage_id WHERE tc.parent_id IN (?1) " +
            "AND tc.dock_province = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);

//    @Query(value = "SELECT tb.source_code `code`, ts.product_name `name`, ts.spec specification, ts.branch_package_num package_specification, ts.spec_units main_unit  " +
//            "FROM t_subpackage ts LEFT JOIN t_plant_box tb ON ts.id = tb.subpackage_id WHERE ts.company_id = ?1", nativeQuery = true)
    // relevance_id 就是 t_plant_process_detail 的 id
    @Query(value = "SELECT ts.batch `code`, ts.product_name `name`, ts.spec specification, ts.branch_package_num package_specification, ts.spec_units main_unit " +
            "FROM t_subpackage ts WHERE ts.company_id = ?1 AND relevance_id = ?2", nativeQuery = true)
    List<JSONObject> dock3(String companyId, String relevanceId);
}
